﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.Common;
using System.Reflection;
using System.Data.OleDb;
using System.Data.OracleClient;
using Dominio;

namespace Persistencia
{
    public class DisciplinaDAO
    {
        protected int _count = 0;
        protected SqlConnection conn = null;
        protected void closeConexao()
        {
            GerenciadorConexao.closeConexao(conn);
            conn = null;
        }
        protected void abrirConexao()
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
        }
        public int selectCountAll()
        {
            return _count;
        }
        #region métodos auxiliares
        private String getNomeTabela() {
            return "Disciplina";
        }
        private String getSelect()
        {
            return "select id, nome from " + this.getNomeTabela();
        }

        protected DbParameter novoParametro(String nome, DbType tipo, Object valor)
        {
            SqlParameter par = new SqlParameter();
            par.ParameterName = nome;
            if (tipo !=null)
                par.DbType = tipo;
            if (valor != null)
                par.Value = valor;
            return par;

        }
        private DisciplinaVO getEntidade(DbDataReader dr)
        {
            DisciplinaVO disciplina = new DisciplinaVO();

            try
            {
                disciplina.id = (int)dr["id"];
                disciplina.Nome = (string)dr["nome"].ToString().Trim();
                
            }
            catch (Exception)
            {
            }
            return disciplina;
        }
        #endregion
        #region métodos acessadores
        public IList<DisciplinaVO> list(string nome)
        {
            if (String.IsNullOrEmpty(nome))
                return this.recuperarDisciplinas();
            else
                return this.recuperarAlunos(nome);
        }
        /// <summary>
        /// Exibe todos os objetos cadastrados.
        /// </summary>
        /// <returns>Retorna a lista de objetos da classe T.</returns>
        private IList<DisciplinaVO> recuperarDisciplinas()
        {
            IList<DisciplinaVO> alunos = new List<DisciplinaVO>();
            conn = GerenciadorConexao.getConexao();

            SqlCommand command = new SqlCommand();
            command.CommandText = this.getSelect();
            command.Connection = conn;

            try
            {
                abrirConexao();
                DbDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        alunos.Add(this.getEntidade(dr));
                    }
                }
                else
                {
                    //não há registros
                }
                dr.Close();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConexao();
            }
            return alunos;
        }
        private List<DisciplinaVO> recuperarAlunos(string nome)
        {
            List<DisciplinaVO> alunos = new List<DisciplinaVO>();

            conn = GerenciadorConexao.getConexao();

            SqlCommand command = new SqlCommand(this.getSelect() + " where nome LIKE '%' + @parNome + '%' ORDER BY nome", conn);

            abrirConexao();
            try
            {
                command.Parameters.Add(this.novoParametro("@parNome", DbType.String, nome));
                DbDataReader dr = command.ExecuteReader();
                try
                {
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            alunos.Add(this.getEntidade(dr));
                        }
                    }
                }
                finally { dr.Close(); }
            }
            finally { closeConexao(); }

            return alunos;
        }
        public DisciplinaVO findByPrimaryKey(int id)
        {
            //Activator.CreateInstance(
            DisciplinaVO vo = new DisciplinaVO();
            conn = GerenciadorConexao.getConexao();
            
            SqlCommand command = new SqlCommand(this.getSelect() + " where id = @parID", conn);

            abrirConexao();
            try
            {
                command.Parameters.Add(this.novoParametro("parID", DbType.Int32,id));
                DbDataReader dr = command.ExecuteReader();
                try
                {
                    if (dr.HasRows)
                    {
                        dr.Read();
                        vo = this.getEntidade(dr);
                    }
                }
                finally { dr.Close(); }
            }
            finally { closeConexao(); }

            return vo;
        }

        #endregion
        #region métodos modificadores
        public int inserir(DisciplinaVO vo)
        {
            int retorno = 0;

            conn = GerenciadorConexao.getConexao();

            SqlCommand command = new SqlCommand();
            command.CommandText =  "INSERT INTO " + this.getNomeTabela() + " (nome) VALUES (@nome)";
            command.Connection = conn;

            command.Parameters.Add(this.novoParametro("nome", DbType.String,vo.Nome));
            

            try
            {
                abrirConexao();
                
                retorno = command.ExecuteNonQuery();
                if (retorno > 0)
                    vo.id = GerenciadorConexao.getId(this.getNomeTabela());
                return retorno;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConexao();
            }
        }
        public virtual int alterar(DisciplinaVO vo)
        {
            int retorno = 0;

            conn = GerenciadorConexao.getConexao();

            SqlCommand command = new SqlCommand();
            command.CommandText = "UPDATE " + this.getNomeTabela() + " set nome = @nome where id = @id";
            command.Connection = conn;

            command.Parameters.Add(this.novoParametro("nome", DbType.String, vo.Nome));
            command.Parameters.Add(this.novoParametro("@id", DbType.Int32, vo.id));
            try
            {
                abrirConexao();
                retorno = command.ExecuteNonQuery();
                return retorno;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConexao();
            }
        }
       
        #endregion
    }
    
}
